﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

using System.Diagnostics;

// this namespace has functions to access the Database
namespace DatabaseLayer
{

    public class DatabaseFunctions
    {
        private String connectionString;

        private String sSource = "DatabaseFunctions";
        private String sLog = "Application";
        
        public DatabaseFunctions()
        {
            //AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData));
            //connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\IVRDatabase.mdf;Integrated Security=True;User Instance=True";

            // AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Visual Studio 2010\\Projects\\IVR2.0\\DatabaseLayer");
            // connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\IVRDatabase.mdf;Integrated Security=True;User Instance=True";

            connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\t-avash\\Documents\\Visual Studio 2010\\Projects\\IVRJunction\\DatabaseLayer\\IVRJunctionDatabase.mdf;Integrated Security=True;User Instance=True";
            
            if (!EventLog.SourceExists(sSource))
                EventLog.CreateEventSource(sSource, sLog);
            EventLog.WriteEntry(sSource, "Constructor Started");
        }


        /*  IVR Junction - Synchronization Service Database Code
         * 
         * 
         * 
         * 
         */

        /* This function is used by Download Service (i.e. Service 3)
           Function can return one of the following status code
   -       -1 => Error
            0 => false, the moderation status is public
            1 => True, the moderation status is private
            2 => is not present in the database
        */
        public int getModerationStatusOfAudioCorrespondingToYouTubeVideoId(String youTubeVideoId)
        {

            SqlConnection sqlConnection = null;
            int statusCode = 2;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT IsPrivate FROM RecordingData WHERE YouTubeVideoId=@YouTubeVideoId AND DownloadedFromSkyDrive=1";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter youTubeVideoIdParameter = new SqlParameter("@YouTubeVideoId", System.Data.SqlDbType.NVarChar);
                        youTubeVideoIdParameter.Value = youTubeVideoId;
                        sqlCommand.Parameters.Add(youTubeVideoIdParameter);
                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                bool isPrivate = (Boolean)sqlDataReader[0];

                                if (isPrivate)
                                    statusCode = 1;
                                else
                                    statusCode = 0;

                            }
                        }
                    }
                }

                    EventLog.WriteEntry(sSource, "Moderation status of an audio corresponding to a YouTube Video Id is fetched",
                        EventLogEntryType.Information);
                
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                statusCode = -1;
                EventLog.WriteEntry(sSource, "Error fetching moderation status of audio corresponding to YouTube Video Id:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                statusCode = -1;
                EventLog.WriteEntry(sSource, "Error fetching moderation status of audio corresponding to YouTube Video Id:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                statusCode = -1;
                EventLog.WriteEntry(sSource, "Error fetching moderation status of audio corresponding to YouTube Video Id:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                statusCode = -1;
                EventLog.WriteEntry(sSource, "Error fetching moderation status of audio corresponding to YouTube Video Id:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return statusCode;
        }

        public Boolean updateModerationStatusForYouTubeVideoId(String youTubeVideoId, bool moderationStatus)
        {
            SqlConnection sqlConnection = null;
            Boolean updateSuccessful = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlUpdateCommand = new SqlCommand())
                    {
                        sqlUpdateCommand.CommandType = System.Data.CommandType.Text;

                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET IsPrivate=@moderationStatus WHERE YouTubeVideoId=@youTubeVideoId AND DownloadedFromSkyDrive=1";
                        sqlUpdateCommand.Connection = sqlConnection;

                        SqlParameter moderationStatusParameter = new SqlParameter("@moderationStatus", System.Data.SqlDbType.Int);
                        moderationStatusParameter.Value = moderationStatus;
                        sqlUpdateCommand.Parameters.Add(moderationStatusParameter);

                        SqlParameter youTubeVideoIdParameter = new SqlParameter("@youTubeVideoId", System.Data.SqlDbType.NVarChar);
                        youTubeVideoIdParameter.Value = youTubeVideoId;
                        sqlUpdateCommand.Parameters.Add(youTubeVideoIdParameter);

                        int outputCode = sqlUpdateCommand.ExecuteNonQuery();

                        if (outputCode == 1)
                        {
                            updateSuccessful = true;
                            EventLog.WriteEntry(sSource, "Moderation status corresponding to YouTube Video Id is updated", EventLogEntryType.Information);
                        }
                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating moderation status corresponding to YouTube Video Id:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error updating moderation status corresponding to YouTube Video Id:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating moderation status corresponding to YouTube Video Id:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error updating moderation status corresponding to YouTube Video Id:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return updateSuccessful;
        }

        public List<Tuple<String, String>> youTubeVideoIdAndAudioFileLocationDownloadedFromCloudStorage()
        {
            SqlConnection sqlConnection = null;
            List<Tuple<String, String>> youTubeVideoIdList = new List<Tuple<String, String>>();

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT YouTubeVideoId, RecordingFileLocation FROM RecordingData WHERE DownloadedFromSkyDrive=1 ORDER BY Datetimenow";
                        sqlCommand.Connection = sqlConnection;

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                String youTubeVideoId = null;
                                String recordingFileLocation = null;
                                if (sqlDataReader[0] != DBNull.Value)
                                {
                                    youTubeVideoId = (String)sqlDataReader[0];
                                }

                                if (sqlDataReader[1] != DBNull.Value)
                                {
                                    recordingFileLocation = (String)sqlDataReader[1];
                                }

                                Tuple<String, String> tuple = new Tuple<string, string>(youTubeVideoId, recordingFileLocation);
                                youTubeVideoIdList.Add(tuple);
                            }

                            EventLog.WriteEntry(sSource, "YouTube Video Id and Audio File Location is fetched for files downloaded from Cloud",
                                EventLogEntryType.Information);

                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                youTubeVideoIdList = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube VideoId and audio file location for files downloaded from Cloud:" +
                    invalidArgumentException.Message, EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                youTubeVideoIdList = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube VideoId and audio file location for files downloaded from Cloud:" +
                    sqlException.Message, EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                youTubeVideoIdList = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube VideoId and audio file location for files downloaded from Cloud:" +
                    argumentException.Message, EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                youTubeVideoIdList = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube VideoId and audio file location for files downloaded from Cloud:" +
                    generalException.Message, EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return youTubeVideoIdList;
        }

        /*  IVR Junction - Moderation Service Database Code
         * 
         * 
         * 
         * 
         */

        public List<Tuple<String, String, Boolean>> getAudioIdVideoIdAndModerationStatus()
        {
            List<Tuple<String, String, Boolean>> databaseInformation = new List<Tuple<String, String, Boolean>>();

            SqlConnection sqlConnection = null;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT RecordingFileLocation, YouTubeVideoId, IsPrivate FROM RecordingData WHERE YouTubeVideoId IS NOT NULL AND DownloadedFromSkyDrive = 0 ORDER BY Datetimenow DESC";
                        sqlCommand.Connection = sqlConnection;

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                databaseInformation.Add(new Tuple<string, string, bool>((String)sqlDataReader[0], (String)sqlDataReader[1],
                                    (Boolean)sqlDataReader[2]));

                            }

                            EventLog.WriteEntry(sSource, "Audio Id, Video Id and moderation status is fetched successfully", EventLogEntryType.Information);

                            // sqlConnection.Close();
                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                databaseInformation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio Id, video Id and moderation status:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                databaseInformation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio Id, video Id and moderation status:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                databaseInformation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio Id, video Id and moderation status:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                databaseInformation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio Id, video Id and moderation status:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return databaseInformation;

        }

        public int getCloudStorageStatusOfAudioFile(String audioFileLocation)
        {
            SqlConnection sqlConnection = null;

            // here if -1 is returned implies that an error occured in the function
            int cloudStorageUploadStatus = -1;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT CloudStorageUploadStatus FROM RecordingData WHERE RecordingFileLocation=@audioFileLocation";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                        audioFileLocationParameter.Value = audioFileLocation;
                        sqlCommand.Parameters.Add(audioFileLocationParameter);

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                cloudStorageUploadStatus = (Int32)sqlDataReader[0];
                            }

                            EventLog.WriteEntry(sSource, "Cloud storage status of audio file is fetched", EventLogEntryType.Information);
                            //sqlConnection.Close();
                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error fetching cloud storage status of audio file:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error fetching cloud storage status of audio file:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error fetching cloud storage status of audio file:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error fetching cloud storage status of audio file:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return cloudStorageUploadStatus;

        }

        public Boolean deleteAudioFileFromDatabase(String audioFileLocation)
        {
            SqlConnection sqlConnection = null;
            Boolean deleteSuccessful = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlDeleteCommand = new SqlCommand())
                    {
                        sqlDeleteCommand.CommandType = System.Data.CommandType.Text;

                        sqlDeleteCommand.CommandText = "DELETE FROM RecordingData WHERE RecordingFileLocation=@audioFileLocation";
                        sqlDeleteCommand.Connection = sqlConnection;

                        SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                        audioFileLocationParameter.Value = audioFileLocation;
                        sqlDeleteCommand.Parameters.Add(audioFileLocationParameter);

                        int outputCode = sqlDeleteCommand.ExecuteNonQuery();


                        if (outputCode != 0)
                        {
                            deleteSuccessful = true;
                            EventLog.WriteEntry(sSource, "Specified audio file deleted from the Database:" + audioFileLocation,
                                EventLogEntryType.Information);
                        }
                    }

                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error deleting specified audio file from database:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error deleting specified audio file from database:" + sqlException.Message,
                     EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error deleting specified audio file from database:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error deleting specified audio file from database:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return deleteSuccessful;

        }

        public Tuple<int, int> getCloudStorageStatusFacebookUploadStatusOfAudioFile(String audioFileLocation)
        {
            SqlConnection sqlConnection = null;

            // here if -1 is returned implies that an error occured in the function
            int cloudStorageUploadStatus = -1;
            int FacebookUploadStatus = -1;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT CloudStorageUploadStatus, FacebookUploadStatus FROM RecordingData WHERE RecordingFileLocation=@audioFileLocation";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                        audioFileLocationParameter.Value = audioFileLocation;
                        sqlCommand.Parameters.Add(audioFileLocationParameter);

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                cloudStorageUploadStatus = (Int32)sqlDataReader[0];
                                FacebookUploadStatus = (Int32)sqlDataReader[1];
                            }

                            EventLog.WriteEntry(sSource, "Facebook upload status and Cloud storage status of audio file is fetched",
                                EventLogEntryType.Information);
                            //sqlConnection.Close();

                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status and cloud storage status of audio file:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status and cloud storage status of audio file:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status and cloud storage status of audio file:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status and cloud storage status of audio file:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            Tuple<int, int> tuple = new Tuple<int, int>(cloudStorageUploadStatus, FacebookUploadStatus);

            return tuple;
        }


        /*
            update moderation status (isPrivate coloumn) in audio DB to false, implies make the audio file public in audio database
            check the CloudStorageUploadStatus, if it is zero then update the status to 1, that is to mark this audio file to upload on cloud Storage.
            If moderation status (isPrivate Coloumn) in audio DB is set to true, implies the audio file is marked private
            Check the CloudStorageUploadStatus, if it is 1 then update the status to 0, that is to unmark this audio file to upload on cloud Storage
        */
        public Boolean updateModerationStatusCloudStorageStatusFBUploadStatusofAudio(String audioFileLocation, bool moderationStatus, bool uploadVideoOnFacebook)
        {
            //int currentCloudStorageStatus = getCloudStorageStatusOfAudioFile(audioFileLocation);
            //int currentFacebookUploadStatus = getFacebookUploadStatusOfAudioFile(audioFileLocation);

            Tuple<int, int> tuple = getCloudStorageStatusFacebookUploadStatusOfAudioFile(audioFileLocation);

            int currentCloudStorageStatus = tuple.Item1;
            int currentFacebookUploadStatus = tuple.Item2;

            Boolean updateSuccessful = false;

            if (currentCloudStorageStatus != -1)
            {
                SqlConnection sqlConnection = null;
                try
                {
                    using (sqlConnection = new SqlConnection(connectionString))
                    {
                        sqlConnection.Open();

                        using (SqlCommand sqlUpdateCommand = new SqlCommand())
                        {
                            sqlUpdateCommand.CommandType = System.Data.CommandType.Text;

                            sqlUpdateCommand.CommandText = "UPDATE RecordingData SET IsPrivate=@moderationStatus, CloudStorageUploadStatus=@uploadStatus, FacebookUploadStatus=@FbUploadStatus WHERE RecordingFileLocation=@audioFileLocation";
                            sqlUpdateCommand.Connection = sqlConnection;

                            SqlParameter moderationStatusParameter = new SqlParameter("@moderationStatus", System.Data.SqlDbType.Int);
                            moderationStatusParameter.Value = moderationStatus;
                            sqlUpdateCommand.Parameters.Add(moderationStatusParameter);

                            SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                            audioFileLocationParameter.Value = audioFileLocation;
                            sqlUpdateCommand.Parameters.Add(audioFileLocationParameter);

                            SqlParameter uploadStatusParameter = new SqlParameter("@uploadStatus", System.Data.SqlDbType.Int);

                            int statusCodeToSet = currentCloudStorageStatus;

                            if (moderationStatus == false && currentCloudStorageStatus == 0)
                                statusCodeToSet = 1;
                            else if (moderationStatus == true && currentCloudStorageStatus == 1)
                                statusCodeToSet = 0;

                            uploadStatusParameter.Value = statusCodeToSet;
                            sqlUpdateCommand.Parameters.Add(uploadStatusParameter);

                            SqlParameter FacebookUploadStatusParameter = new SqlParameter("@FbUploadStatus", System.Data.SqlDbType.Int);

                            statusCodeToSet = currentFacebookUploadStatus;

                            // if video is public and if video is not yet marked for uploading or not yet uploaded on facebook and if the moderator wants to upload the video on facebook
                            if (moderationStatus == false && currentFacebookUploadStatus == 0 && uploadVideoOnFacebook == true)
                                statusCodeToSet = 1;
                            else if (moderationStatus == true && currentFacebookUploadStatus == 1)
                                statusCodeToSet = 0;

                            FacebookUploadStatusParameter.Value = statusCodeToSet;
                            sqlUpdateCommand.Parameters.Add(FacebookUploadStatusParameter);

                            sqlUpdateCommand.ExecuteNonQuery();

                            updateSuccessful = true;
                            EventLog.WriteEntry(sSource, "Moderation status, cloud storage status and facebook upload status is updated",
                            EventLogEntryType.Information);
                        }
                    }
                }
                catch (System.InvalidOperationException invalidArgumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating moderation status, cloud storage status and facebook upload status:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
                }
                catch (SqlException sqlException)
                {
                    EventLog.WriteEntry(sSource, "Error updating moderation status, cloud storage status and facebook upload status:" + sqlException.Message,
                    EventLogEntryType.Error);
                }
                catch (ArgumentException argumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating moderation status, cloud storage status and facebook upload status:" + argumentException.Message,
                    EventLogEntryType.Error);
                }
                catch (Exception generalException)
                {
                    EventLog.WriteEntry(sSource, "Error updating moderation status, cloud storage status and facebook upload status:" + generalException.Message,
                    EventLogEntryType.Error);
                }
                finally
                {
                    if (sqlConnection != null)
                        sqlConnection.Close();
                }

            }

            return updateSuccessful;
        }
       
        public List<String> getAudioFilesToBeUploadedOnCloudStorage()
        {
            SqlConnection sqlConnection = null;
            List<String> audioFileLocation = new List<string>();

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT RecordingFileLocation FROM RecordingData WHERE CloudStorageUploadStatus=1 ORDER BY Datetimenow DESC";
                        sqlCommand.Connection = sqlConnection;

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                audioFileLocation.Add((String)sqlDataReader[0]);
                            }

                            EventLog.WriteEntry(sSource, "Audio files to be uploaded on cloud are successfully fetched", EventLogEntryType.Information);
                            //sqlConnection.Close();

                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations to be uploaded on cloud" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations to be uploaded on cloud" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations to be uploaded on cloud" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations to be uploaded on cloud" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return audioFileLocation;
        }

        /* CloudStorageUpdateStatus = 0 => not uploaded on cloud storage
         * CloudStorageUpdateStatus = 1 => Has to be uploaded on cloud storage
         * CloudStorageUpdateStatus = 2 => Uploaded on cloud storage
         * Here CloudStorage can be DropBox or SkyDrive
         */
        public Boolean updateCloudStorageStatus(String audioFileLocation, int statusCode)
        {
            SqlConnection sqlConnection = null;
            Boolean updateSuccessful = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlUpdateCommand = new SqlCommand())
                    {
                        sqlUpdateCommand.CommandType = System.Data.CommandType.Text;

                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET CloudStorageUploadStatus=@statusCode WHERE RecordingFileLocation=@audioFileLocation";
                        sqlUpdateCommand.Connection = sqlConnection;

                        SqlParameter uploadStatusParameter = new SqlParameter("@statusCode", System.Data.SqlDbType.Int);
                        uploadStatusParameter.Value = statusCode;
                        sqlUpdateCommand.Parameters.Add(uploadStatusParameter);

                        SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                        audioFileLocationParameter.Value = audioFileLocation;
                        sqlUpdateCommand.Parameters.Add(audioFileLocationParameter);

                        int outputCode = sqlUpdateCommand.ExecuteNonQuery();

                        if (outputCode == 1)
                        {
                            updateSuccessful = true;
                            EventLog.WriteEntry(sSource, "Cloud storage status is updated", EventLogEntryType.Information);
                        }
                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating cloud storage status of audio file:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error updating cloud storage status of audio file:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating cloud storage status of audio file:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error updating cloud storage status of audio file:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return updateSuccessful;
        }

        public List<String> getYouTubeVideoIdOfAudioFilesToBeUploadedOnFacebook()
        {
            SqlConnection sqlConnection = null;
            List<String> youTubeVideoId = new List<string>();

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT YouTubeVideoId FROM RecordingData WHERE FacebookUploadStatus=1 ORDER BY Datetimenow DESC";
                        sqlCommand.Connection = sqlConnection;

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                youTubeVideoId.Add((String)sqlDataReader[0]);
                            }
                            EventLog.WriteEntry(sSource, "YouTube video id of audio files to be uploaded on facebook is successfully fetched",
                                EventLogEntryType.Information);
                            //sqlConnection.Close();
                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                youTubeVideoId = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube Video Id of audiop files to be uploaded on Facebook" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                youTubeVideoId = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube Video Id of audiop files to be uploaded on Facebook" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                youTubeVideoId = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube Video Id of audiop files to be uploaded on Facebook" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                youTubeVideoId = null;
                EventLog.WriteEntry(sSource, "Error fetching YouTube Video Id of audiop files to be uploaded on Facebook" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return youTubeVideoId;
        }

        public Boolean updateFacebookUploadStatus(String youTubeVideoId, int statusCode)
        {
            SqlConnection sqlConnection = null;
            Boolean updateSuccessful = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlUpdateCommand = new SqlCommand())
                    {
                        sqlUpdateCommand.CommandType = System.Data.CommandType.Text;

                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET FacebookUploadStatus=@statusCode WHERE YouTubeVideoId=@YouTubeVideoId";
                        sqlUpdateCommand.Connection = sqlConnection;

                        SqlParameter uploadStatusParameter = new SqlParameter("@statusCode", System.Data.SqlDbType.Int);
                        uploadStatusParameter.Value = statusCode;
                        sqlUpdateCommand.Parameters.Add(uploadStatusParameter);

                        SqlParameter youTubeVideoIdParameter = new SqlParameter("@YouTubeVideoId", System.Data.SqlDbType.NVarChar);
                        youTubeVideoIdParameter.Value = youTubeVideoId;
                        sqlUpdateCommand.Parameters.Add(youTubeVideoIdParameter);

                        int outputCode = sqlUpdateCommand.ExecuteNonQuery();

                        if (outputCode != 0)
                        {
                            updateSuccessful = true;
                            EventLog.WriteEntry(sSource, "Facebook upload status of audio file is updated", EventLogEntryType.Information);
                        }
                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating facebook upload status of audio file:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error updating facebook upload status of audio file:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating facebook upload status of audio file:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error updating facebook upload status of audio file:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return updateSuccessful;
        }

        public int getFacebookUploadStatusOfAudioFile(String audioFileLocation)
        {
            SqlConnection sqlConnection = null;

            // here if -1 is returned implies that an error occured in the function
            int facebookUploadStatus = -1;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT FacebookUploadStatus FROM RecordingData WHERE RecordingFileLocation=@audioFileLocation";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                        audioFileLocationParameter.Value = audioFileLocation;
                        sqlCommand.Parameters.Add(audioFileLocationParameter);

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                facebookUploadStatus = (Int32)sqlDataReader[0];
                            }

                            EventLog.WriteEntry(sSource, "Facebook upload status of audio file is fetched", EventLogEntryType.Information);
                            //sqlConnection.Close();

                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status of audio file:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status of audio file:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status of audio file:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error fetching facebook upload status of audio file:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return facebookUploadStatus;

        }

        /*  IVR Junction - YouTube Upload Service Database Code
         * 
         * 
         * 
         * 
         */

        /* The function is used to tackle the hard shutdown of the computer. As the Upload on YouTube Service
         * stores the location of audio file(s) in a queue to upload it ( or them) on YouTube and set the YouTube Upload Status flag
         * in the table as 1, it may be possible that an unexpected failure or shutdown let the status of the audio files in queue remain 1 for forever.
         * Hence, we would run this function at the start of service to track the unexpected shutdown of service where service was unable
         * to call the Stop method.
         * if on starting the services, YoutubeUploadStatus is one and YouTubeVideoId is null then make the value 0
         * else if YouTubeVideoId is not null then make the value 2
        */
        public void fixInvalidStateOfYouTubeUploadStatus()
        {
            SqlConnection sqlConnection = null;


            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlUpdateCommand = new SqlCommand())
                    {
                        sqlUpdateCommand.CommandType = System.Data.CommandType.Text;


                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET YouTubeUploadStatus=0 WHERE YouTubeUploadStatus = 1 AND YouTubeVideoId is NULL";
                        sqlUpdateCommand.Connection = sqlConnection;

                        int a = sqlUpdateCommand.ExecuteNonQuery();

                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET YouTubeUploadStatus=2 WHERE YouTubeUploadStatus = 1 AND YouTubeVideoId is NOT NULL";
                        int b = sqlUpdateCommand.ExecuteNonQuery();

                        EventLog.WriteEntry(sSource, "Invalid state of YouTubeUploadStatus is fixed", EventLogEntryType.Information);

                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error fixing invalid state of YouTubeUploadStatus:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);

            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error fixing invalid state of YouTubeUploadStatus:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error fixing invalid state of YouTubeUploadStatus:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error fixing invalid state of YouTubeUploadStatus:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }
        }

        public Boolean updateStatusofYouTubeUploadForAudioFile(string audioFileLocation, int uploadStatus, string YouTubeVideoId)
        {
            SqlConnection sqlConnection = null;
            Boolean uploadSuccessful = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlUpdateCommand = new SqlCommand())
                    {
                        sqlUpdateCommand.CommandType = System.Data.CommandType.Text;

                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET YouTubeUploadStatus=@uploadStatus, YouTubeVideoId=@YouTubeVideoId WHERE RecordingFileLocation=@audioFileLocation";
                        sqlUpdateCommand.Connection = sqlConnection;

                        SqlParameter uploadStatusParameter = new SqlParameter("@uploadStatus", System.Data.SqlDbType.Int);
                        uploadStatusParameter.Value = uploadStatus;
                        sqlUpdateCommand.Parameters.Add(uploadStatusParameter);

                        SqlParameter audioFileLocationParameter = new SqlParameter("@audioFileLocation", System.Data.SqlDbType.NVarChar);
                        audioFileLocationParameter.Value = audioFileLocation;
                        sqlUpdateCommand.Parameters.Add(audioFileLocationParameter);

                        SqlParameter youTubeVideoIdParameter = new SqlParameter("@YouTubeVideoId", System.Data.SqlDbType.NVarChar);

                        if (YouTubeVideoId == null)
                            youTubeVideoIdParameter.Value = DBNull.Value;
                        else
                            youTubeVideoIdParameter.Value = YouTubeVideoId;

                        sqlUpdateCommand.Parameters.Add(youTubeVideoIdParameter);
                        int outputCode = sqlUpdateCommand.ExecuteNonQuery();


                        if (outputCode == 1)
                        {
                            uploadSuccessful = true;
                            EventLog.WriteEntry(sSource, "YouTube Upload Status is updated for audio file", EventLogEntryType.Information);
                        }

                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating YouTube upload status of audio file:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error updating YouTube upload status of audio file:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error updating YouTube upload status of audio file:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error updating YouTube upload status of audio file:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return uploadSuccessful;
        }

        public List<Tuple<String, String>> getAudioFilesNotUploadedOnYouTube()
        {
            SqlConnection sqlConnection = null;
            List<Tuple<String, String>> audioFileLocation = new List<Tuple<String, String>>();

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT RecordingFileLocation, PhoneNumber FROM RecordingData WHERE YouTubeUploadStatus=0 AND DownloadedFromSkyDrive=0 ORDER BY Datetimenow DESC";
                        sqlCommand.Connection = sqlConnection;

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                Tuple<String, String> tuple = new Tuple<String, String>((String)sqlDataReader[0], (String)sqlDataReader[1]);
                                audioFileLocation.Add(tuple);
                            }

                            // sqlConnection.Close();
                            EventLog.WriteEntry(sSource, "Audio files not uploaded on Youtube is successfully fetched", EventLogEntryType.Information);

                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations not uploaded on YouTube:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations not uploaded on YouTube:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations not uploaded on YouTube:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                audioFileLocation = null;
                EventLog.WriteEntry(sSource, "Error fetching audio file locations not uploaded on YouTube:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return audioFileLocation;
        }

        /*  Sample Voice Forum Database Code
         * 
         * 
         * 
         * 
         */

        public bool addAnalytics(String sessionId, String callerId, String pageName, String action, String identifier)
        {
            SqlConnection sqlConnection = null;

            bool isAdditionSuccess = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "INSERT INTO Analytics VALUES (@SessionId, @CallerId, @TimeStamp, @Page, @Action, @Identifier)";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter sessionIdParameter = new SqlParameter("@SessionId", System.Data.SqlDbType.NVarChar, 50);
                        sessionIdParameter.Value = sessionId;
                        sqlCommand.Parameters.Add(sessionIdParameter);

                        SqlParameter callerIdParameter = new SqlParameter("@CallerId", System.Data.SqlDbType.NVarChar, 50);
                        if (callerId == null)
                            callerIdParameter.Value = "NOT DETECTED";
                        else
                            callerIdParameter.Value = callerId;
                        sqlCommand.Parameters.Add(callerIdParameter);

                        SqlParameter systemDateParameter = new SqlParameter("@TimeStamp", System.Data.SqlDbType.DateTime);
                        systemDateParameter.Value = System.DateTime.Now;
                        sqlCommand.Parameters.Add(systemDateParameter);

                        SqlParameter pageParameter = new SqlParameter("@Page", System.Data.SqlDbType.NVarChar, 50);
                        pageParameter.Value = pageName;
                        sqlCommand.Parameters.Add(pageParameter);

                        SqlParameter actionParameter = new SqlParameter("@Action", System.Data.SqlDbType.NVarChar);
                        actionParameter.Value = action;
                        sqlCommand.Parameters.Add(actionParameter);

                        SqlParameter identifierParameter = new SqlParameter("@Identifier", System.Data.SqlDbType.NVarChar);
                        if (identifier == null)
                            identifierParameter.Value = DBNull.Value;
                        else
                            identifierParameter.Value = identifier;
                        sqlCommand.Parameters.Add(identifierParameter);

                        sqlCommand.ExecuteNonQuery();

                        EventLog.WriteEntry(sSource, "Analytics data is added", EventLogEntryType.Information);

                        isAdditionSuccess = true;

                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error adding Analytics data:" + invalidArgumentException.Message, EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error adding Analytics data:" + sqlException.Message, EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error adding Analytics data:" + argumentException.Message, EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error adding Analytics data:" + generalException.Message, EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return isAdditionSuccess;

        }

        /*
        public List<String> moderatedAudioFileLocations()
        {
            SqlConnection sqlConnection = null;
            List<String> recordingURL = new List<string>();

            try
            {
                sqlConnection = new SqlConnection(connectionString);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandType = System.Data.CommandType.Text;


                sqlCommand.CommandText = "SELECT RecordingURL FROM RecordingData WHERE IsPrivate=0 ORDER BY Datetimenow DESC";
                sqlCommand.Connection = sqlConnection;

                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                while (sqlDataReader.Read())
                {
                    recordingURL.Add((String)sqlDataReader[0]);
                }

                EventLog.WriteEntry(sSource, "Moderated audio file locations fetch is successful", EventLogEntryType.Information);
                //sqlConnection.Close();

            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching moderated audio file locations:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching moderated audio file locations:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching moderated audio file locations:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching moderated audio file locations:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return recordingURL;
        }
        */

        public String getModeratedFileForPlayback(String sessionID)
        {
            SqlConnection sqlConnection = null;
            String recordingURL = null;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT Top(1) RecordingURL FROM RecordingData WHERE IsPrivate=0 AND RecordingURL NOT IN (Select RecordingURL From CallerListenData Where SessionID=@SessionID) ORDER BY Datetimenow DESC";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter sessionIDParameter = new SqlParameter("@SessionID", System.Data.SqlDbType.NVarChar);
                        sessionIDParameter.Value = sessionID;
                        sqlCommand.Parameters.Add(sessionIDParameter);

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                recordingURL = (String)sqlDataReader[0];
                            }

                            // This indicates that no value is there in the database

                            if (recordingURL == null)
                                recordingURL = "";

                            EventLog.WriteEntry(sSource, "TOp Moderated audio file URL is successfully fetched", EventLogEntryType.Information);
                            //sqlConnection.Close();

                        }
                    }
                }
            }

            catch (System.InvalidOperationException invalidArgumentException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching top moderated audio file location:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }

            catch (SqlException sqlException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching top moderated audio file location:" + sqlException.Message,
                    EventLogEntryType.Error);
            }

            catch (ArgumentException argumentException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching top moderated audio file locations" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                recordingURL = null;
                EventLog.WriteEntry(sSource, "Error fetching top moderated audio file location:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return recordingURL;

        }

        public int saveRecordedData(string fileLocation, string recordingURL, string callerId, string branchId, int youTubeUploadStatus,
            bool downloadedFromSkyDrive, bool isPrivate, String youTubeVideoId, int cloudStorageUploadStatus, int facebookUploadStatus)
        {

            SqlConnection sqlConnection = null;

            int statusMessage = 1;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "INSERT INTO RecordingData values (@RecordingFileLocation, @RecordingURL,@PhoneNumber,@DateTimeNow,@BranchId,@YouTubeUploadStatus," +
                        "@DownloadedFromSkyDrive,@IsPrivate,@YouTubeVideoId,@CloudStorageUploadStatus,@FacebookUploadStatus)";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter recordingFileLocationParameter = new SqlParameter("@RecordingFileLocation", System.Data.SqlDbType.NVarChar);
                        recordingFileLocationParameter.Value = fileLocation;
                        sqlCommand.Parameters.Add(recordingFileLocationParameter);

                        SqlParameter recordingURLParameter = new SqlParameter("@RecordingURL", System.Data.SqlDbType.NVarChar);
                        recordingURLParameter.Value = recordingURL;
                        sqlCommand.Parameters.Add(recordingURLParameter);

                        SqlParameter phoneNumberParameter = new SqlParameter("@PhoneNumber", System.Data.SqlDbType.NVarChar, 50);
                        phoneNumberParameter.Value = callerId;
                        sqlCommand.Parameters.Add(phoneNumberParameter);


                        SqlParameter systemDateParameter = new SqlParameter("@DateTimeNow", System.Data.SqlDbType.DateTime);
                        systemDateParameter.Value = System.DateTime.Now;
                        sqlCommand.Parameters.Add(systemDateParameter);

                        SqlParameter branchIdParameter = new SqlParameter("@BranchId", System.Data.SqlDbType.NVarChar, 50);
                        branchIdParameter.Value = branchId;
                        sqlCommand.Parameters.Add(branchIdParameter);

                        SqlParameter youTubeUploadStatusParameter = new SqlParameter("@YouTubeUploadStatus", System.Data.SqlDbType.Int);
                        youTubeUploadStatusParameter.Value = youTubeUploadStatus;
                        sqlCommand.Parameters.Add(youTubeUploadStatusParameter);

                        SqlParameter downloadedFromSkyDriveParameter = new SqlParameter("@DownloadedFromSkyDrive", System.Data.SqlDbType.Bit);
                        downloadedFromSkyDriveParameter.Value = downloadedFromSkyDrive;
                        sqlCommand.Parameters.Add(downloadedFromSkyDriveParameter);

                        SqlParameter isPrivateParameter = new SqlParameter("@IsPrivate", System.Data.SqlDbType.Bit);
                        isPrivateParameter.Value = isPrivate;
                        sqlCommand.Parameters.Add(isPrivateParameter);

                        SqlParameter youTubeVideoIdParameter = new SqlParameter("@YouTubeVideoId", System.Data.SqlDbType.NVarChar);
                        youTubeVideoIdParameter.Value = youTubeVideoId;
                        sqlCommand.Parameters.Add(youTubeVideoIdParameter);

                        if (phoneNumberParameter.Value == null)
                        {
                            phoneNumberParameter.Value = "notdefined";
                        }

                        if (youTubeVideoIdParameter.Value == null)
                        {
                            youTubeVideoIdParameter.Value = DBNull.Value;

                        }

                        SqlParameter cloudStorageUploadStatusParameter = new SqlParameter("@CloudStorageUploadStatus", System.Data.SqlDbType.Int);
                        cloudStorageUploadStatusParameter.Value = cloudStorageUploadStatus;
                        sqlCommand.Parameters.Add(cloudStorageUploadStatusParameter);

                        SqlParameter FacebookUploadStatusParameter = new SqlParameter("@FacebookUploadStatus", System.Data.SqlDbType.Int);
                        FacebookUploadStatusParameter.Value = facebookUploadStatus;
                        sqlCommand.Parameters.Add(FacebookUploadStatusParameter);


                        sqlCommand.ExecuteNonQuery();
                        EventLog.WriteEntry(sSource, "Recorded data saved successfully", EventLogEntryType.Information);
                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error saving recorded data:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
                statusMessage = 2;
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error saving recorded data:" + sqlException.Message,
                    EventLogEntryType.Error);
                statusMessage = 3;
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error saving recorded data:" + argumentException.Message,
                    EventLogEntryType.Error);

                statusMessage = 4;
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error saving recorded data:" + generalException.Message,
                    EventLogEntryType.Error);

            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }


            return statusMessage;

        }

        public bool insertCallerListenData(String sessionId, String recordingURL)
        {
            SqlConnection sqlConnection = null;

            bool statusMessage = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "INSERT INTO CallerListenData values (@SessionID, @RecordingURL)";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter sessionIDParameter = new SqlParameter("@SessionID", System.Data.SqlDbType.NVarChar, 50);
                        sessionIDParameter.Value = sessionId;
                        sqlCommand.Parameters.Add(sessionIDParameter);

                        SqlParameter recordingURLParameter = new SqlParameter("@RecordingURL", System.Data.SqlDbType.NVarChar);
                        recordingURLParameter.Value = recordingURL;
                        sqlCommand.Parameters.Add(recordingURLParameter);

                        sqlCommand.ExecuteNonQuery();
                        statusMessage = true;
                        EventLog.WriteEntry(sSource, "Listen Data added to the table", EventLogEntryType.Information);
                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error adding listen data to the table:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error adding listen data to the table:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error adding listen data to the table:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error adding listen data to the table:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }


            return statusMessage;

        }

        public bool deleteCallerListenData(String sessionId)
        {
            SqlConnection sqlConnection = null;

            bool statusMessage = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "DELETE FROM CallerListenData WHERE SessionID= @SessionID";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter sessionIDParameter = new SqlParameter("@SessionID", System.Data.SqlDbType.NVarChar, 50);
                        sessionIDParameter.Value = sessionId;
                        sqlCommand.Parameters.Add(sessionIDParameter);

                        sqlCommand.ExecuteNonQuery();
                        statusMessage = true;
                        EventLog.WriteEntry(sSource, "Listen Data emptied for the current session", EventLogEntryType.Information);
                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error deleting listen data for the current session:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error deleting listen data for the current session:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error deleting listen data for the current session:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error deleting listen data for the current session:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return statusMessage;
        }


        /*  IVR Junction UI Database Code
         * 
         * 
         * 
         * 
         */

        public bool insertOrUpdateIVRJunctionConfigurationTable(String branchId, String IVRJunctionFolderLocation, String YTDeveloperKey, String YTAccountName,
            String YTUsername, String YTPassword, String YTVideoTitle, String YTKeywords,
            String YTDescription, String YTLocation, String DBLocalFolder)
        {
            SqlConnection sqlConnection = null;
            int countRecords = -1;
            SqlCommand sqlCommand = null;
            bool isOperationSuccess = false;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {

                    sqlConnection.Open();
                    using (sqlCommand = new SqlCommand())
                    {
                        sqlCommand.CommandType = System.Data.CommandType.Text;
                        sqlCommand.Connection = sqlConnection;

                        sqlCommand.CommandText = "SELECT Count(*) FROM IVRJunctionConfiguration";
                        
                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                countRecords = ((Int32)sqlDataReader[0]);
                            }
                            sqlDataReader.Close();
                            //sqlConnection.Close();
                            EventLog.WriteEntry(sSource, "Count of configuration information is obtained", EventLogEntryType.Information);
                        }

                    }
                }
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error counting configuration information:" + generalException.Message,
                    EventLogEntryType.Error);
                Console.WriteLine(generalException);
                if (sqlConnection != null)
                    sqlConnection.Close();
            }



            if (countRecords == -1)
                isOperationSuccess = false;
            else
            {
                try
                {
                    using (sqlConnection = new SqlConnection(connectionString))
                    {
                        sqlConnection.Open();
                        using (sqlCommand = new SqlCommand())
                        {
                            sqlCommand.CommandType = System.Data.CommandType.Text;
                            sqlCommand.Connection = sqlConnection;

                            if (countRecords == 0)
                            {
                                sqlCommand.CommandText = "INSERT INTO IVRJunctionConfiguration (BranchId, IVRJunctionFolderLocation, YTDeveloperKey, YTAccountName, YTUserName, YTPassword," +
                                    "YTTitle, YTKeywords, YTDescription, YTLocation, " +
                                    "DBLocalFolder) values (@BranchId, @IVRJunctionFolderLocation, @YTDeveloperKey,@YTAccountName,@YTUsername,@YTPassword," +
                                  "@Title,@Keywords,@Description, @Location,@DBLocalFolder)";

                            }
                            else
                            {
                                sqlCommand.CommandText = "UPDATE IVRJunctionConfiguration SET BranchId= @BranchId, IVRJunctionFolderLocation=@IVRJunctionFolderLocation," +
                                    " YTDeveloperKey=@YTDeveloperKey, YTAccountName= @YTAccountName," +
                                    "YTUserName=@YTUsername, YTPassword=@YTPassword, " +
                                 "YTTitle=@Title, YTKeywords=@Keywords,YTDescription=@Description, YTLocation = @Location," +
                                 " DBLocalFolder=@DBLocalFolder";

                            }

                            SqlParameter branchIdParameter = new SqlParameter("@BranchId", System.Data.SqlDbType.NVarChar);
                            if (branchId == null)
                                branchIdParameter.Value = DBNull.Value;
                            else
                                branchIdParameter.Value = branchId;
                            sqlCommand.Parameters.Add(branchIdParameter);

                            SqlParameter IVRJunctionFolderLocationParameter = new SqlParameter("@IVRJunctionFolderLocation", System.Data.SqlDbType.NVarChar);
                            if (IVRJunctionFolderLocation == null)
                                IVRJunctionFolderLocationParameter.Value = DBNull.Value;
                            else
                                IVRJunctionFolderLocationParameter.Value = IVRJunctionFolderLocation;
                            sqlCommand.Parameters.Add(IVRJunctionFolderLocationParameter);

                            SqlParameter YTDeveloperKeyParameter = new SqlParameter("@YTDeveloperKey", System.Data.SqlDbType.NVarChar);
                            if (YTDeveloperKey == null)
                                YTDeveloperKeyParameter.Value = DBNull.Value;
                            else
                                YTDeveloperKeyParameter.Value = YTDeveloperKey;
                            sqlCommand.Parameters.Add(YTDeveloperKeyParameter);

                            SqlParameter YTAccountNameParameter = new SqlParameter("@YTAccountName", System.Data.SqlDbType.NVarChar);
                            if (YTAccountName == null)
                                YTAccountNameParameter.Value = DBNull.Value;
                            else
                                YTAccountNameParameter.Value = YTAccountName;
                            sqlCommand.Parameters.Add(YTAccountNameParameter);


                            SqlParameter YTUsernameParameter = new SqlParameter("@YTUsername", System.Data.SqlDbType.NVarChar);
                            if (YTUsername == null)
                                YTUsernameParameter.Value = DBNull.Value;
                            else
                                YTUsernameParameter.Value = YTUsername;
                            sqlCommand.Parameters.Add(YTUsernameParameter);

                            SqlParameter YTPasswordParameter = new SqlParameter("@YTPassword", System.Data.SqlDbType.NVarChar);
                            if (YTPassword == null)
                                YTPasswordParameter.Value = DBNull.Value;
                            else
                                YTPasswordParameter.Value = YTPassword;
                            sqlCommand.Parameters.Add(YTPasswordParameter);


                            SqlParameter TitleParameter = new SqlParameter("@Title", System.Data.SqlDbType.NVarChar);
                            if (YTVideoTitle == null)
                                TitleParameter.Value = DBNull.Value;
                            else
                                TitleParameter.Value = YTVideoTitle;
                            sqlCommand.Parameters.Add(TitleParameter);

                            SqlParameter KeywordsParameter = new SqlParameter("@Keywords", System.Data.SqlDbType.NVarChar);
                            if (YTKeywords == null)
                                KeywordsParameter.Value = DBNull.Value;
                            else
                                KeywordsParameter.Value = YTKeywords;
                            sqlCommand.Parameters.Add(KeywordsParameter);

                            SqlParameter DescriptionParameter = new SqlParameter("@Description", System.Data.SqlDbType.NVarChar);
                            if (YTDescription == null)
                                DescriptionParameter.Value = DBNull.Value;
                            else
                                DescriptionParameter.Value = YTDescription;
                            sqlCommand.Parameters.Add(DescriptionParameter);

                            SqlParameter LocationParameter = new SqlParameter("@Location", System.Data.SqlDbType.NVarChar);
                            if (YTLocation == null)
                                LocationParameter.Value = DBNull.Value;
                            else
                                LocationParameter.Value = YTLocation;
                            sqlCommand.Parameters.Add(LocationParameter);



                            SqlParameter DBLocalFolderParameter = new SqlParameter("@DBLocalFolder", System.Data.SqlDbType.NVarChar);
                            if (DBLocalFolder == null)
                                DBLocalFolderParameter.Value = DBNull.Value;
                            else
                                DBLocalFolderParameter.Value = DBLocalFolder;
                            sqlCommand.Parameters.Add(DBLocalFolderParameter);


                            sqlCommand.ExecuteNonQuery();
                            isOperationSuccess = true;
                            EventLog.WriteEntry(sSource, "Configuration information updated or inserted", EventLogEntryType.Information);
                        }
                    }
                }
                catch (System.InvalidOperationException invalidArgumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating configuration information:" + invalidArgumentException.Message,
                        EventLogEntryType.Error);

                }
                catch (SqlException sqlException)
                {
                    EventLog.WriteEntry(sSource, "Error updating configuration information:" + sqlException.Message,
                        EventLogEntryType.Error);

                }
                catch (ArgumentException argumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating configuration information:" + argumentException.Message,
                        EventLogEntryType.Error);

                }
                catch (Exception generalException)
                {
                    EventLog.WriteEntry(sSource, "Error updating configuration information:" + generalException.Message,
                        EventLogEntryType.Error);
                }
                finally
                {
                    if (sqlConnection != null)
                        sqlConnection.Close();
                }
            }


            return isOperationSuccess;

        }

        public bool insertOrUpdateFacebookToken(String facebookToken)
        {
            SqlConnection sqlConnection = null;
            int countRecords = -1;
            SqlCommand sqlCommand = null;
            bool isOperationSuccess = false;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (sqlCommand = new SqlCommand())
                    {
                        
                        sqlCommand.CommandType = System.Data.CommandType.Text;
                        sqlCommand.Connection = sqlConnection;

                        sqlCommand.CommandText = "SELECT Count(*) FROM IVRJunctionConfiguration";
                        
                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                countRecords = ((Int32)sqlDataReader[0]);
                            }
                            sqlDataReader.Close();
                            //sqlConnection.Close();
                            EventLog.WriteEntry(sSource, "Count of configuration information is obtained", EventLogEntryType.Information);
                        }
                    }
                }
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error counting configuration information:" + generalException.Message,
                    EventLogEntryType.Error);
                Console.WriteLine(generalException);
                if (sqlConnection != null)
                    sqlConnection.Close();
            }



            if (countRecords == -1)
                isOperationSuccess = false;
            else
            {
                try
                {
                    using (sqlConnection = new SqlConnection(connectionString))
                    {
                        sqlConnection.Open();
                        using (sqlCommand = new SqlCommand())
                        {
                            sqlCommand.CommandType = System.Data.CommandType.Text;
                            sqlCommand.Connection = sqlConnection;

                            if (countRecords == 0)
                            {
                                sqlCommand.CommandText = "INSERT INTO IVRJunctionConfiguration (facebookAccessToken) values (@facebookAccessToken)";

                            }
                            else
                            {
                                sqlCommand.CommandText = "UPDATE IVRJunctionConfiguration SET facebookAccessToken= @facebookAccessToken";

                            }

                            SqlParameter facebookAccessTokenParameter = new SqlParameter("@facebookAccessToken", System.Data.SqlDbType.NVarChar);
                            if (facebookToken == null)
                                facebookAccessTokenParameter.Value = DBNull.Value;
                            else
                                facebookAccessTokenParameter.Value = facebookToken;
                            sqlCommand.Parameters.Add(facebookAccessTokenParameter);

                            sqlCommand.ExecuteNonQuery();
                            isOperationSuccess = true;
                            EventLog.WriteEntry(sSource, "Facebook Access Token updated or inserted", EventLogEntryType.Information);
                        }
                    }
                }
                catch (System.InvalidOperationException invalidArgumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating Facebook Access Token:" + invalidArgumentException.Message,
                        EventLogEntryType.Error);

                }
                catch (SqlException sqlException)
                {
                    EventLog.WriteEntry(sSource, "Error updating Facebook Access Token:" + sqlException.Message,
                        EventLogEntryType.Error);

                }
                catch (ArgumentException argumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating Facebook Access Token:" + argumentException.Message,
                        EventLogEntryType.Error);

                }
                catch (Exception generalException)
                {
                    EventLog.WriteEntry(sSource, "Error updating Facebook Access Token:" + generalException.Message,
                        EventLogEntryType.Error);
                }
                finally
                {
                    if (sqlConnection != null)
                        sqlConnection.Close();
                }
            }


            return isOperationSuccess;

        }


        
        public bool updateVideoPropertiesInConfigurationTable(String YTVideoTitle, String YTKeywords,
            String YTDescription, String YTLocation)
        {
            SqlConnection sqlConnection = null;
            bool isOperationSuccess = false;

            try
            {

                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.CommandType = System.Data.CommandType.Text;
                        sqlCommand.CommandText = "UPDATE IVRJunctionConfiguration SET YTTitle=@Title, YTKeywords=@Keywords," +
                            "YTDescription=@Description, YTLocation = @Location";
                        sqlCommand.Connection = sqlConnection;

                        SqlParameter TitleParameter = new SqlParameter("@Title", System.Data.SqlDbType.NVarChar);
                        TitleParameter.Value = YTVideoTitle;
                        sqlCommand.Parameters.Add(TitleParameter);

                        SqlParameter KeywordsParameter = new SqlParameter("@Keywords", System.Data.SqlDbType.NVarChar);
                        KeywordsParameter.Value = YTKeywords;
                        sqlCommand.Parameters.Add(KeywordsParameter);

                        SqlParameter DescriptionParameter = new SqlParameter("@Description", System.Data.SqlDbType.NVarChar);
                        DescriptionParameter.Value = YTDescription;
                        sqlCommand.Parameters.Add(DescriptionParameter);

                        SqlParameter LocationParameter = new SqlParameter("@Location", System.Data.SqlDbType.NVarChar);
                        LocationParameter.Value = YTLocation;
                        sqlCommand.Parameters.Add(LocationParameter);

                        sqlCommand.ExecuteNonQuery();
                        isOperationSuccess = true;
                        EventLog.WriteEntry(sSource, "Video properties in configuration table are updated", EventLogEntryType.Information);

                    }
                }
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error updating video properties in configuration table" + generalException.Message,
                    EventLogEntryType.Error);
                if (sqlConnection != null)
                    sqlConnection.Close();
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }


            return isOperationSuccess;

        }

        
        public bool insertOrUpdateGenericConfiguration(bool isModerationOnline, bool isSynchronizeBranch, bool isConfigureFacebook)
        {
            SqlConnection sqlConnection = null;
            int countRecords = -1;
            SqlCommand sqlCommand = null;
            bool isOperationSuccess = false;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (sqlCommand = new SqlCommand())
                    {

                        sqlCommand.CommandType = System.Data.CommandType.Text;


                        sqlCommand.CommandText = "SELECT Count(*) FROM IVRJunctionConfiguration";
                        sqlCommand.Connection = sqlConnection;

                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {

                            while (sqlDataReader.Read())
                            {
                                countRecords = ((Int32)sqlDataReader[0]);
                            }
                            sqlDataReader.Close();
                            //sqlConnection.Close();
                            EventLog.WriteEntry(sSource, "Counted configuration information", EventLogEntryType.Information);
                        }
                    }
                }
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error counting generic configuration information:" + generalException.Message,
                    EventLogEntryType.Error);
                if (sqlConnection != null)
                    sqlConnection.Close();
            }


            if (countRecords == -1)
            {
                isOperationSuccess = false;
            }
            else
            {
                try
                {
                    using (sqlConnection = new SqlConnection(connectionString))
                    {
                        sqlConnection.Open();
                        using (sqlCommand = new SqlCommand())
                        {
                            sqlCommand.CommandType = System.Data.CommandType.Text;
                            sqlCommand.Connection = sqlConnection;

                            if (countRecords == 0)
                            {
                                sqlCommand.CommandText = "INSERT INTO IVRJunctionConfiguration (isModerationOnline, isSynchronizeBranch,isConfigureFacebook)"
                                + " values (@isModerationOnline, @isSynchronizeBranch,@isConfigureFacebook)";

                            }
                            else
                            {
                                sqlCommand.CommandText = "UPDATE IVRJunctionConfiguration SET isModerationOnline= @isModerationOnline, isSynchronizeBranch=@isSynchronizeBranch" +
                                    ", isConfigureFacebook = @isConfigureFacebook";

                            }

                            SqlParameter isModerationOnlineParameter = new SqlParameter("@isModerationOnline", System.Data.SqlDbType.Int);
                            isModerationOnlineParameter.Value = isModerationOnline;
                            sqlCommand.Parameters.Add(isModerationOnlineParameter);

                            SqlParameter isSynchronizeBranchParameter = new SqlParameter("@isSynchronizeBranch", System.Data.SqlDbType.Int);
                            isSynchronizeBranchParameter.Value = isSynchronizeBranch;
                            sqlCommand.Parameters.Add(isSynchronizeBranchParameter);

                            SqlParameter isConfigureFacebookParameter = new SqlParameter("@isConfigureFacebook", System.Data.SqlDbType.Int);
                            isConfigureFacebookParameter.Value = isConfigureFacebook;
                            sqlCommand.Parameters.Add(isConfigureFacebookParameter);

                            sqlCommand.ExecuteNonQuery();
                            isOperationSuccess = true;
                            EventLog.WriteEntry(sSource, "Generic configuration information is updated/inserted", EventLogEntryType.Information);
                        }
                    }
                }
                catch (System.InvalidOperationException invalidArgumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating or inserting generic configuration information:" + invalidArgumentException.Message,
                        EventLogEntryType.Error);

                }
                catch (SqlException sqlException)
                {
                    EventLog.WriteEntry(sSource, "Error updating or inserting generic configuration information:" + sqlException.Message,
                        EventLogEntryType.Error);

                }
                catch (ArgumentException argumentException)
                {
                    EventLog.WriteEntry(sSource, "Error updating or inserting generic configuration information:" + argumentException.Message,
                        EventLogEntryType.Error);

                }
                catch (Exception generalException)
                {
                    EventLog.WriteEntry(sSource, "Error updating or inserting generic configuration information:" + generalException.Message,
                        EventLogEntryType.Error);
                }
                finally
                {
                    if (sqlConnection != null)
                        sqlConnection.Close();
                }
            }


            return isOperationSuccess;

        }
    
        public bool deleteIVRJunctionConfiguration()
        {
            SqlConnection sqlConnection = null;
            Boolean deleteSuccessful = false;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlDeleteCommand = new SqlCommand())
                    {
                        sqlDeleteCommand.CommandType = System.Data.CommandType.Text;



                        sqlDeleteCommand.CommandText = "DELETE FROM IVRJunctionConfiguration";
                        sqlDeleteCommand.Connection = sqlConnection;

                        int outputCode = sqlDeleteCommand.ExecuteNonQuery();


                        deleteSuccessful = true;
                        EventLog.WriteEntry(sSource, "Configuration information deleted", EventLogEntryType.Information);

                    }
                }
            }
            catch (System.InvalidOperationException invalidArgumentException)
            {
                EventLog.WriteEntry(sSource, "Error deleting configuration information:" + invalidArgumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (SqlException sqlException)
            {
                EventLog.WriteEntry(sSource, "Error deleting configuration information:" + sqlException.Message,
                    EventLogEntryType.Error);
            }
            catch (ArgumentException argumentException)
            {
                EventLog.WriteEntry(sSource, "Error deleting configuration information:" + argumentException.Message,
                    EventLogEntryType.Error);
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error deleting configuration information:" + generalException.Message,
                    EventLogEntryType.Error);
            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return deleteSuccessful;


        }

        public bool updateDataGridRow(String recordingFileLocation, bool isPrivate, String phoneNumber, DateTime dateTimeNow)
        {
            bool incrementSuccessful = false;
            SqlConnection sqlConnection = null;
            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();

                    using (SqlCommand sqlUpdateCommand = new SqlCommand())
                    {
                        sqlUpdateCommand.CommandType = System.Data.CommandType.Text;

                        sqlUpdateCommand.CommandText = "UPDATE RecordingData SET IsPrivate=@IsPrivate, PhoneNumber=@PhoneNumber, DateTimeNow=@DateTimeNow WHERE RecordingFileLocation=@RecordingFileLocation";

                        sqlUpdateCommand.Connection = sqlConnection;

                        SqlParameter recordingFileLocationParameter = new SqlParameter("@RecordingFileLocation", System.Data.SqlDbType.NVarChar);
                        recordingFileLocationParameter.Value = recordingFileLocation;
                        sqlUpdateCommand.Parameters.Add(recordingFileLocationParameter);

                        SqlParameter isPrivateParameter = new SqlParameter("@IsPrivate", System.Data.SqlDbType.Bit);
                        isPrivateParameter.Value = isPrivate;
                        sqlUpdateCommand.Parameters.Add(isPrivateParameter);

                        SqlParameter phoneNumberParameter = new SqlParameter("@PhoneNumber", System.Data.SqlDbType.NVarChar);
                        phoneNumberParameter.Value = phoneNumber;
                        sqlUpdateCommand.Parameters.Add(phoneNumberParameter);

                        SqlParameter dateTimeNowparameter = new SqlParameter("@DateTimeNow", System.Data.SqlDbType.DateTime);
                        dateTimeNowparameter.Value = dateTimeNow;
                        sqlUpdateCommand.Parameters.Add(dateTimeNowparameter);

                        sqlUpdateCommand.ExecuteNonQuery();
                        incrementSuccessful = true;
                    }
                }
            }
            catch (Exception generalException)
            {
                EventLog.WriteEntry(sSource, "Error incrementing Totalplays:" + generalException.Message, EventLogEntryType.Error);

            }
            finally
            {
                if (sqlConnection != null)
                    sqlConnection.Close();
            }
            return incrementSuccessful;
        }

        /*  Database Function Used By Multiple Modules
         * 
         * 
         * 
         * 
         */

        // This function returns the configuration data from the table
        /* Return is null if there is a sql error
         * Return is a filled Tuple if data is available in the table
         */
        public Tuple<String, String, Tuple<String, String, String, String>, Tuple<String, String, String, String>,
                        String, Tuple<bool, bool, bool>,String> getIVRJunctionConfiguration()
        {

            SqlConnection sqlConnection = null;

            String branchId = null;
            int count = 0;
            Tuple<String, String, Tuple<String, String, String, String>, Tuple<String, String, String, String>,
                String, Tuple<bool, bool, bool>,String> mainTuple = null;

            try
            {
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand())
                    {

                        sqlCommand.CommandType = System.Data.CommandType.Text;

                        sqlCommand.CommandText = "SELECT BranchId, IVRJunctionFolderLocation, YTDeveloperKey, YTAccountName, YTUserName, YTPassword, " +
                                                 " YTTitle, YTKeywords, YTDescription, YTLocation, " +
                                                 " DBLocalFolder, isModerationOnline, isSynchronizeBranch, isConfigureFacebook, facebookAccessToken " +
                                                 "FROM IVRJunctionConfiguration";

                        sqlCommand.Connection = sqlConnection;
                        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                        {


                            while (sqlDataReader.Read())
                            {

                                if (sqlDataReader[0] != DBNull.Value)
                                    branchId = ((String)sqlDataReader[0]);

                                String IVRJunctionFolderLocation = null;

                                if (sqlDataReader[1] != DBNull.Value)
                                    IVRJunctionFolderLocation = ((String)sqlDataReader[1]);

                                //byte[] youTubeAccessToken = null;
                                //if (sqlDataReader[6] != DBNull.Value)
                                //    youTubeAccessToken = ((byte[])sqlDataReader[6]);



                                String YTDeveloperKey = null, YTAccountName = null, YTUsername = null, YTPassword = null;
                                if (sqlDataReader[2] != DBNull.Value)
                                    YTDeveloperKey = (String)sqlDataReader[2];
                                if (sqlDataReader[3] != DBNull.Value)
                                    YTAccountName = (String)sqlDataReader[3];
                                if (sqlDataReader[4] != DBNull.Value)
                                    YTUsername = (String)sqlDataReader[4];
                                if (sqlDataReader[5] != DBNull.Value)
                                    YTPassword = (String)sqlDataReader[5];

                                Tuple<String, String, String, String> youTubeConfigurationTuple =
                                    Tuple.Create(YTDeveloperKey, YTAccountName, YTUsername, YTPassword);

                                String YTTitle = null, YTKeywords = null, YTDescription = null, YTLocation = null;
                                if (sqlDataReader[6] != DBNull.Value)
                                    YTTitle = (String)sqlDataReader[6];
                                if (sqlDataReader[7] != DBNull.Value)
                                    YTKeywords = (String)sqlDataReader[7];
                                if (sqlDataReader[8] != DBNull.Value)
                                    YTDescription = (String)sqlDataReader[8];
                                if (sqlDataReader[9] != DBNull.Value)
                                    YTLocation = (String)sqlDataReader[9];

                                Tuple<String, String, String, String> youTubeVideoDataTuple =
                                    Tuple.Create(YTTitle, YTKeywords, YTDescription, YTLocation);

                                //byte[] dropBoxAccessToken = null;
                                //if (sqlDataReader[13] != DBNull.Value)
                                //    dropBoxAccessToken = ((byte[])sqlDataReader[13]);

                                String DBLocalFolder = null;
                                if (sqlDataReader[10] != DBNull.Value)
                                    DBLocalFolder = (String)sqlDataReader[10];


                                Boolean isModerationOnline = false, isSynchronizeBranch = false, isConfigureFacebook = false;
                                if (sqlDataReader[11] != DBNull.Value)
                                    isModerationOnline = (Boolean)sqlDataReader[11];
                                if (sqlDataReader[12] != DBNull.Value)
                                    isSynchronizeBranch = (Boolean)sqlDataReader[12];
                                if (sqlDataReader[13] != DBNull.Value)
                                    isConfigureFacebook = (Boolean)sqlDataReader[13];

                                Tuple<bool, bool, bool> genericConfigurationTuple =
                                    Tuple.Create(isModerationOnline, isSynchronizeBranch, isConfigureFacebook);

                                String facebookAccessToken = null;
                                if (sqlDataReader[14] != DBNull.Value)
                                    facebookAccessToken = (String)sqlDataReader[14];


                                mainTuple
                                 = new Tuple<String, String, Tuple<String, String, String, String>, Tuple<String, String, String, String>,
                                String, Tuple<bool, bool, bool>, String>(branchId, IVRJunctionFolderLocation, youTubeConfigurationTuple,
                                youTubeVideoDataTuple, DBLocalFolder, genericConfigurationTuple, facebookAccessToken);


                                count++;
                                break;
                            }

                            EventLog.WriteEntry(sSource, "Configuration data is successfully read", EventLogEntryType.Information);

                        }
                    }
                }
            }
            catch (Exception generalException)
            {
                mainTuple = null;
                count = -1;
                EventLog.WriteEntry(sSource, "Error reading configuration data" + generalException.Message,
                    EventLogEntryType.Error);
                if (sqlConnection != null)
                    sqlConnection.Close();
            }

            return mainTuple;

        }
    }
}
